'''
@Description: In User Settings Edit
@Author: your name
@Date: 2019-08-22 16:23:40
@LastEditTime : 2020-02-14 18:04:10
@LastEditors  : Please set LastEditors
'''
from ...pao_python.pao.data import process_db, dataframe_to_list, DataProcess, data_to_string_date
from ...pao_python.pao.service.security.security_utility import get_current_account_id
from ...service.common import insert_data, find_data, update_data, delete_data, get_condition, get_info, get_cur_time, get_code, get_url_upload_url
import pandas as pd
import uuid
import datetime
import re
import hashlib
import uuid
from enum import Enum
from ...pao_python.pao.service.data.mongo_db import MongoService, MongoFilter, C, N, F, as_date
from openpyxl import Workbook, load_workbook
from ...service.buss_pub.bill_manage import BillManageService, OperationType, TypeId, Status
from ...service.mongo_bill_service import MongoBillFilter
from ...service.constant import AccountType, PayType, PayStatue, AccountStatus, plat_id
from ...models.financial_manage import FinancialAccount
from ...service.buss_mis.financial_account import FinancialAccountObject
import os
import time
# from cv2 import cv2 as cv
import urllib
import urllib.request


class ExcelManageService(MongoService):
    def __init__(self, db_addr, db_port, db_name, db_user, db_pwd, inital_password, session, web_path, upload_file):
        '''构造函数'''
        DataProcess.__init__(self, db_addr, db_port, db_name, db_user, db_pwd)
        # self.db_name = db_name
        self.inital_password = inital_password
        self.session = session
        self.web_path = web_path
        self.upload_file = upload_file
        self.bill_manage_service = BillManageService(
            db_addr, db_port, db_name, db_user, db_pwd, inital_password, session)

    def export_excel(self, method, condition):
        wb = Workbook()
        ws = wb.active
        ws.title = "test sheet"
        ws.cell(row=1, column=1).value = "A"
        ws.cell(row=1, column=2).value = "C"
        # ws.append({'第一列': 'This is A1', "测试": 'This is C1'})
        ws.append({1: 'This is A1', 2: 'This is C1'})
        # for i in data:
        #     ws.append(i)
        url = "C:/Users/yzy/Desktop/test/t.xlsx"
        wb.save(url)
        wb.close
        return ''

    def import_excel_admin(self):
        ''' 导入行政区划'''
        url = "C:/Users/yzy/Desktop/test/行政区划导入.xlsx"
        # 打开一个workbook
        wb = load_workbook(filename=url)

        # 获取当前活跃的worksheet,默认就是第一个worksheet
        # ws = wb.active

        # 当然也可以使用下面的方法

        # 获取所有表格(worksheet)的名字
        sheets = wb.get_sheet_names()
        # 第一个表格的名称

        sheet_first = sheets[0]
        # 获取特定的worksheet
        ws = wb.get_sheet_by_name(sheet_first)

        column_number = ws.max_column  # 最大的列
        row_number = ws.max_row  # 最大的行

        # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
        rows = list(ws.rows)
        columns = list(ws.columns)
        Data = ws.cell(row=1, column=column_number).value

        for i in range(1, row_number):    # 从第二行开始遍历
            # 要保存的对象
            l1 = {}    # 完整的一条添加数据
            for j in range(0, column_number):    # 从第一列开始读取
                data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
                if j == 0:
                    l1['name'] = data_value
                if j == 1:
                    l1['english_name'] = data_value
                if j == 2:
                    l1['code'] = str(data_value)
                if j == 3:
                    l1['short_name'] = data_value
                if j == 4:
                    l1['capital'] = data_value
                if j == 5:
                    l1['location'] = {'lon': str(data_value), 'lat': ''}
                if j == 6:
                    l1['location']['lat'] = str(data_value)
                if j == 7:
                    l1['division_level'] = str(data_value)
                if j == 8:
                    parent_id = ''
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('code') == str(data_value)))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_Administration_Division')
                    if len(res) > 0:
                        parent_id = res[0]['id']
                    l1['parent_id'] = parent_id
                if j == 9:
                    l1['comment'] = data_value
            l1['organization_id'] = '0b96b62e-e363-11e9-93c4-a0a4c57e9eb1'
            data = get_info(l1, self.session)
            # 逐条插入（因为怕找不到上级Id）
            try:
                # count = a.cur.executemany(add_sql, l2)
                print('最终结果数据》》》', i)
                bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                            TypeId.businessArea.value, data, 'PT_Administration_Division')
                # if bill_id:
                #     res = 'Success'
                #     return res
            except Exception as e:
                print(str(e))
        return 'success'
        # 这是效果是一样的，通俗一点理解，上面的正规一点
        # for row in ws.iter_rows():
        #     ii += 1
        #     # 去掉数据上面的标签 ID name password
        #     if (ii == 1):
        #         continue
        #     l1 = []  # 完整的一条添加数据
        #     for cell in row:
        #         l1.append(cell.value)
        #     l2.append(l1)

        # 关闭连接

    def import_excel_org(self):
        ''' 导入组织机构'''
        url = "C:\\Users\\ymq\\Desktop\\双鸭山验收\\双鸭山机构信息管理导入模板.xlsx"
        # 打开一个workbook
        wb = load_workbook(filename=url)

        # 获取当前活跃的worksheet,默认就是第一个worksheet
        # ws = wb.active

        # 当然也可以使用下面的方法

        # 获取所有表格(worksheet)的名字
        sheets = wb.get_sheet_names()
        # 第一个表格的名称

        sheet_first = sheets[0]
        # 获取特定的worksheet
        ws = wb.get_sheet_by_name(sheet_first)

        column_number = ws.max_column  # 最大的列
        row_number = ws.max_row  # 最大的行

        # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
        rows = list(ws.rows)
        columns = list(ws.columns)
        Data = ws.cell(row=1, column=column_number).value

        for i in range(1, row_number):    # 从第二行开始遍历
            # 要保存的对象
            l1 = {}    # 完整的一条添加数据
            for j in range(0, column_number):    # 从第一列开始读取
                data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
                if j == 0:
                    l1['name'] = data_value
                if j == 1:
                    l1['organization_info'] = {"code": str(data_value), "telephone": "", "organization_nature": "", "lon": '', "lat": '', "super_org_id": "",
                                               "personnel_category": "福利院", "sys_personnel_category": "", "address": "", "comment": "", "legal_person": "", "business_license_url": []}
                if j == 2:
                    l1['organization_info']['address'] = data_value
                if j == 3:
                    l1['organization_info']['lon'] = str(data_value)
                if j == 4:
                    l1['organization_info']['lat'] = str(data_value)
                if j == 5:
                    l1['organization_info']['legal_person'] = data_value
                if j == 6:
                    l1['organization_info']['telephone'] = data_value
                if j == 7:
                    # 双鸭山的类别存在sys_personnel_category
                    l1['organization_info']['sys_personnel_category'] = data_value
                if j == 8:
                    l1['organization_info']['organization_nature'] = data_value
                if j == 9:
                    admin_id = ''
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('code') == str(data_value)))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_Administration_Division')
                    if len(res) > 0:
                        admin_id = res[0]['id']
                    l1['admin_area_id'] = admin_id
                if j == 10:
                    org_id = '0b96b62e-e363-11e9-93c4-a0a4c57e9eb1'
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('organization_info.code') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        org_id = res[0]['id']
                    l1['organization_info']['super_org_id'] = org_id
                if j == 11:
                    l1['organization_info']['comment'] = data_value
                if j == 12:
                    # 南海的类别存在 personnel_category 新增
                    l1['organization_info']['personnel_category'] = data_value

            l1['organization_id'] = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'
            l1['personnel_type'] = '2'
            l1['qualification_info'] = []
            # 组织机构数据
            data = get_info(l1, self.session)
            datas = data
            tables = 'PT_User'
            # 组织类型为福利院
            if data['organization_info']['personnel_category'] == '福利院':
                # 新增一个真实账户
                # 真实账户对象
                real_account_data = FinancialAccountObject(
                    AccountType.account_real, data['id'], None, AccountStatus.normal, 1, AccountType.account_real, {}, 0)
                real_account = get_info(
                    real_account_data.to_dict(), self.session)
                data['agent_account'] = real_account['id']
                data['main_account'] = real_account['id']
                datas = [data, real_account]
                tables = ['PT_User', FinancialAccount().name]

            # 组织类型为服务商
            if data['organization_info']['personnel_category'] == '服务商':
                # 新增一个真实账户 + 补贴账户
                # 真实账户对象
                real_account_data = FinancialAccountObject(
                    AccountType.account_real, data['id'], None, AccountStatus.normal, 1, AccountType.account_real, {}, 0)
                real_account = get_info(
                    real_account_data.to_dict(), self.session)
                data['agent_account'] = real_account['id']
                data['main_account'] = real_account['id']
                # 补贴账户
                subsidy_account_data = FinancialAccountObject(
                    AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 2, AccountType.account_subsidy, {}, 0)
                subsidy_account = get_info(
                    subsidy_account_data.to_dict(), self.session)
                datas = [[data], [real_account, subsidy_account]]
                tables = ['PT_User', FinancialAccount().name]
            # 逐条插入（因为怕找不到上级Id）
            try:
                # count = a.cur.executemany(add_sql, l2)
                print('最终结果数据》》》', i)
                bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                            TypeId.user.value, datas, tables)
                # if bill_id:
                #     res = 'Success'
                #     return res
            except Exception as e:
                print(str(e))
        return 'success'

    def import_excel_user(self):
        ''' 导入人员（长者）'''
        url = "C:/Users/yzy/Desktop/test/锦程社区-20190921005019.xlsx"
        # 打开一个workbook
        wb = load_workbook(filename=url)

        # 获取当前活跃的worksheet,默认就是第一个worksheet
        # ws = wb.active

        # 当然也可以使用下面的方法

        # 获取所有表格(worksheet)的名字
        sheets = wb.get_sheet_names()
        # 第一个表格的名称

        sheet_first = sheets[0]
        # 获取特定的worksheet
        ws = wb.get_sheet_by_name(sheet_first)

        column_number = ws.max_column  # 最大的列
        row_number = ws.max_row  # 最大的行

        # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
        rows = list(ws.rows)
        columns = list(ws.columns)
        Data = ws.cell(row=1, column=column_number).value
        personnel_info = {
            "name": "",
            "personnel_category": "长者",  # 双鸭山默认全是长者
            "sex": "",
            "telephone": "",
            "date_birth": "",
            "nation": "",
            "native_place": "",
            "address": "",
            "card_number": "",
            "remarks": "",
            "role_id": "8ed260f6-e355-11e9-875e-a0a4c57e9ebe",
            "family_name": "",
            "card_name": "",
            "id_card_address": ""
        }
        for i in range(1, row_number):    # 从第二行开始遍历
            # 要保存的对象
            l1 = {}    # 完整的一条添加数据
            l1['personnel_info'] = personnel_info
            l1['organization_id'] = '0b96b62e-e363-11e9-93c4-a0a4c57e9eb1'  # 双鸭山
            remarks = ''
            role = ''
            for j in range(0, column_number):    # 从第一列开始读取
                data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
                # print('data_value>>',data_value)
                if j == 0:
                    admin_id = ''
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('code') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_Administration_Division')
                    if len(res) > 0:
                        admin_id = res[0]['id']
                    l1['admin_area_id'] = admin_id
                if j == 1:
                    l1['personnel_info']['name'] = data_value
                    l1['name'] = data_value
                if j == 2:
                    l1['personnel_info']['sex'] = data_value
                if j == 3:
                    if isinstance(data_value, str):
                        l1['personnel_info']['date_birth'] = datetime.datetime.strptime(
                            str(data_value), '%Y-%m-%d')
                    else:
                        l1['personnel_info']['date_birth'] = data_value
                if j == 4:
                    l1['id_card'] = data_value
                    l1['id_card_type'] = '身份证'
                if j == 11:
                    l1['personnel_info']['address'] = data_value
                if j == 17:
                    l1['personnel_info']['telephone'] = data_value
                if j == 18:
                    l1['personnel_info']['nation'] = data_value
                if j == 5 or j == 6 or j == 7 or j == 8 or j == 9 or j == 10 or j == 19 or j == 20 or j == 21 or j == 22 or j == 23 or j == 24 or j == 25 or j == 26 or j == 27:
                    if data_value:
                        remarks = remarks + ',' + data_value
                # 以下是新增的三个字段：人员类型、所属组织、工作人员时所属机构类型
                if j == 28:
                    l1['personnel_info']['personnel_category'] = data_value
                if j == 29:
                    # 所属机构
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('code') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        l1['organization_id'] = res[0]['id']
                if j == 30:
                    role = data_value
            l1['personnel_type'] = '1'
            l1['personnel_info']['remarks'] = remarks
            l1['login_info'] = [{
                "login_type": "account",
                "login_check": {
                    "account_name": "",
                    "password": "27bfaa8f76fe30db4d3d2fec408d0b29a772bdd3bf5ee330951233473d94d0e9"
                }
            }]
            data = get_info(l1, self.session)
            # 新增账户信息（1个机构储蓄、1个app储蓄、1个真实账户、1个补贴账户）
            # 机构储蓄对象
            org_account_data = FinancialAccountObject(
                AccountType.account_recharg_wel, data['id'], l1['organization_id'], AccountStatus.normal, 1, AccountType.account_recharg_wel, {}, 0)
            org_account = get_info(org_account_data.to_dict(), self.session)
            # app储蓄对象
            app_account_data = FinancialAccountObject(
                AccountType.account_recharg_app, data['id'], plat_id, AccountStatus.normal, 2, AccountType.account_recharg_app, {}, 0)
            app_account = get_info(app_account_data.to_dict(), self.session)
            # 真实账户对象
            real_account_data = FinancialAccountObject(
                AccountType.account_real, data['id'], None, AccountStatus.normal, 3, AccountType.account_real, {}, 0)
            real_account = get_info(real_account_data.to_dict(), self.session)
            # 补贴账户对象
            subsidy_account_data = FinancialAccountObject(
                AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 4, AccountType.account_subsidy, {}, 0)
            subsidy_account = get_info(
                subsidy_account_data.to_dict(), self.session)
            tables = ['PT_User', FinancialAccount().name]
            datas = [[data], [org_account, app_account,
                              real_account, subsidy_account]]
            if l1['personnel_info']['personnel_category'] == '工作人员':
                # 插入set_role表
                role_id = '8f9809cc-e355-11e9-9d97-a0a4c57e9ebe'
                if role == '服务商':
                    role_id = '8f9809cc-e355-11e9-9d97-a0a4c57e9ebe'
                if role == '幸福院':
                    role_id = '8f3524cc-e355-11e9-9f87-a0a4c57e9ebe'
                if role == '民政局':
                    role_id = '8f1d4064-e355-11e9-b3a2-a0a4c57e9ebe'
                if role == '福利院':
                    role_id = '8e271ff0-e355-11e9-91a6-a0a4c57e9ebe'
                set_role = {'role_id': role_id,
                            'principal_account_id': data['id'], 'role_of_account_id': l1['organization_id']}
                set_role_data = get_info(set_role, self.session)
                datas.append(set_role_data)
                tables.append('PT_Set_Role')
            # 逐条插入（因为怕找不到上级Id）
            try:
                # count = a.cur.executemany(add_sql, l2)
                print('最终结果数据》》》', i)
                bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                            TypeId.user.value, datas, tables)
                # if bill_id:
                #     res = 'Success'
                #     return res
            except Exception as e:
                print(e)
        return res

    def change_user_data(self):
        ''' 建立已加入的用户及机构的账户信息 '''
        res = ''
        # 查询没有建立账户的人员信息和组织机构
        _filter = MongoBillFilter()
        _filter.lookup_bill('PT_Financial_Account', 'id', 'user_id', 'financial_account')\
            .match_bill((C('financial_account') == []))\
            .project({'_id': 0})
        res_data = self.query(_filter, 'PT_User')
        print('res_data>>', len(res_data))
        # 判断是人员还是组织
        if len(res_data) > 0:
            for i in range(0, len(res_data)):
                data = get_info(res_data[i], self.session)
                datas = []
                # 人员
                if data['personnel_type'] == '1':
                    org = data['organization_id']
                    # 查询人员所属set_role表的组织机构
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('principal_account_id') == data['id']))\
                        .project({'_id': 0})
                    set_role_data = self.query(_filter, 'PT_Set_Role')
                    if len(set_role_data) > 0:
                        org = set_role_data[0]['role_of_account_id']
                    # 新增账户信息（1个机构储蓄、1个app储蓄、1个真实账户、1个补贴账户）
                    # 机构储蓄对象
                    org_account_data = FinancialAccountObject(
                        AccountType.account_recharg_wel, data['id'], org, AccountStatus.normal, 1, AccountType.account_recharg_wel, {}, 0)
                    org_account = get_info(
                        org_account_data.to_dict(), self.session)
                    # app储蓄对象
                    app_account_data = FinancialAccountObject(
                        AccountType.account_recharg_app, data['id'], plat_id, AccountStatus.normal, 2, AccountType.account_recharg_app, {}, 0)
                    app_account = get_info(
                        app_account_data.to_dict(), self.session)
                    # 真实账户对象
                    real_account_data = FinancialAccountObject(
                        AccountType.account_real, data['id'], None, AccountStatus.normal, 3, AccountType.account_real, {}, 0)
                    real_account = get_info(
                        real_account_data.to_dict(), self.session)
                    # 补贴账户对象
                    subsidy_account_data = FinancialAccountObject(
                        AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 4, AccountType.account_subsidy, {}, 0)
                    subsidy_account = get_info(
                        subsidy_account_data.to_dict(), self.session)
                    datas = [org_account, app_account,
                             real_account, subsidy_account]
                # 组织
                if res_data[i]['personnel_type'] == '2':
                    # 组织机构数据
                    # 组织类型为福利院
                    if data['organization_info']['personnel_category'] == '福利院':
                        # 新增一个真实账户
                        # 真实账户对象
                        real_account_data = FinancialAccountObject(
                            AccountType.account_real, data['id'], None, AccountStatus.normal, 1, AccountType.account_real, {}, 0)
                        real_account = get_info(
                            real_account_data.to_dict(), self.session)
                        data['agent_account'] = real_account['id']
                        data['main_account'] = real_account['id']
                        datas = [real_account]

                    # 组织类型为服务商
                    if data['organization_info']['personnel_category'] == '服务商':
                        # 新增一个真实账户 + 补贴账户
                        # 真实账户对象
                        real_account_data = FinancialAccountObject(
                            AccountType.account_real, data['id'], None, AccountStatus.normal, 1, AccountType.account_real, {}, 0)
                        real_account = get_info(
                            real_account_data.to_dict(), self.session)
                        data['agent_account'] = real_account['id']
                        data['main_account'] = real_account['id']
                        # 补贴账户
                        subsidy_account_data = FinancialAccountObject(
                            AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 2, AccountType.account_subsidy, {}, 0)
                        subsidy_account = get_info(
                            subsidy_account_data.to_dict(), self.session)
                        datas = [real_account, subsidy_account]
                try:
                    # count = a.cur.executemany(add_sql, l2)
                    print('最终结果数据》》》', i)
                    bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                                TypeId.user.value, datas, FinancialAccount().name)
                    # if bill_id:
                    #     res = 'Success'
                    #     return res
                except Exception as e:
                    print(e)
        return res

    def import_excel_activity(self):
        ''' 导入活动信息'''
        url = "C:/Users/yzy/Desktop/test/佛山活动数据模版.xlsx"
        # 打开一个workbook
        wb = load_workbook(filename=url)

        # 获取当前活跃的worksheet,默认就是第一个worksheet
        # ws = wb.active

        # 当然也可以使用下面的方法

        # 获取所有表格(worksheet)的名字
        sheets = wb.get_sheet_names()
        # 第一个表格的名称

        sheet_first = sheets[0]
        # 获取特定的worksheet
        ws = wb.get_sheet_by_name(sheet_first)

        column_number = ws.max_column  # 最大的列
        row_number = ws.max_row  # 最大的行

        # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
        rows = list(ws.rows)
        columns = list(ws.columns)
        Data = ws.cell(row=1, column=column_number).value

        for i in range(1, row_number):    # 从第二行开始遍历
            # 要保存的对象
            l1 = {}    # 完整的一条添加数据
            for j in range(0, column_number):    # 从第一列开始读取
                data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
                if j == 0:
                    l1['activity_name'] = data_value
                if j == 1:
                    l1['photo'] = [data_value]
                if j == 2:
                    # 所属机构
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        l1['organization_id'] = res[0]['id']
                    else:
                        l1['organization_id'] = 'null'
                if j == 3:
                    l1['max_quantity'] = int(data_value)
                if j == 4:
                    l1['begin_date'] = data_value
                if j == 5:
                    l1['end_date'] = data_value
                if j == 6:
                    if data_value:
                        l1['address'] = data_value
                    else:
                        l1['address'] = ""
                if j == 7:
                    l1['activity_room'] = data_value
                if j == 8:
                    l1['contacts'] = data_value
                if j == 9:
                    l1['phone'] = str(data_value)
                if j == 10:
                    if data_value:
                        l1['amount'] = data_value
                    else:
                        l1['amount'] = '0'
                if j == 11:
                    if data_value:
                        l1['introduce'] = data_value
                    else:
                        l1['introduce'] = ""
            l1['activity_type_id'] = 'f393a563-fab8-11e9-9901-a0a4c57e9ebe'
            l1['is_enable'] = True
            l1['is_sign_in'] = True
            data = get_info(l1, self.session)
            # 逐条插入（因为怕找不到上级Id）
            try:
                # count = a.cur.executemany(add_sql, l2)
                print('最终结果数据》》》', i)
                bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                            TypeId.businessArea.value, data, 'PT_Activity')
                # if bill_id:
                #     res = 'Success'
                #     return res
            except Exception as e:
                print(str(e))
        return 'success'

    def change_org_photo(self):
        _filter = MongoBillFilter()
        _filter.match_bill((C('personnel_type') == '2')
                           & (C('organization_info.personnel_category') == '幸福院'))\
            .project({'_id': 0})
        data_res = self.query(_filter, 'PT_User')
        url_ = 'https://www.e-health100.com/api/attachment/agencyphoto/'
        if len(data_res) > 0:
            i = 0
            for data in data_res:
                i = i+1
                code = data['organization_info']['code']
                url_logo = url_ + str(code) + '_logoPhoto'
                url1 = url_ + str(code) + '1_id'
                url2 = url_ + str(code) + '2_id'
                url3 = url_ + str(code) + '3_id'
                url4 = url_ + str(code) + '4_id'
                url5 = url_ + str(code) + '5_id'
                picture_list = [url_logo, url1, url2, url3, url4, url5]

                data['organization_info']['picture_list'] = picture_list

                def process_func(db):
                    update_data(db, 'PT_User', data, {
                                'id': data['id'], 'bill_status': 'valid'})
                process_db(self.db_addr, self.db_port, self.db_name,
                           process_func, self.db_user, self.db_pwd)
                # print(data)
                print('更新第》》》', i)
        return 'success'

    def import_excel_service_record(self):
        ''' 从导入服务记录数据'''
        url = "C:/Users/Administrator/Desktop/服务记录.xlsx"
        # 打开一个workbook
        wb = load_workbook(filename=url)

        # 获取当前活跃的worksheet,默认就是第一个worksheet
        # ws = wb.active

        # 当然也可以使用下面的方法

        # 获取所有表格(worksheet)的名字
        sheets = wb.get_sheet_names()
        # 第一个表格的名称

        sheet_first = sheets[0]
        # 获取特定的worksheet
        ws = wb.get_sheet_by_name(sheet_first)

        column_number = ws.max_column  # 最大的列
        row_number = ws.max_row  # 最大的行

        # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
        rows = list(ws.rows)
        columns = list(ws.columns)
        Data = ws.cell(row=1, column=column_number).value

        for i in range(1, row_number):    # 从第二行开始遍历
            # 要保存的对象
            l1 = {}    # 完整的一条添加数据
            for j in range(0, column_number):    # 从第一列开始读取
                data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
                if j == 0:
                    # 服务商id
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        l1['organization_id'] = res[0]['id']
                    else:
                        l1['organization_id'] = 'null'
                if j == 1:
                    organization_id = ''
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == rows[i][0].value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        organization_id = res[0]['id']
                    # 服务产品id
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == data_value) & (C('organization_id') == organization_id))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_Service_Product')
                    if len(res) > 0:
                        l1['service_product_id'] = res[0]['id']
                    else:
                        l1['service_product_id'] = 'null'
                if j == 2:
                    if isinstance(data_value, str):
                        l1['start_date'] = datetime.datetime.strptime(
                            str(data_value), '%Y-%m-%d')
                    else:
                        l1['start_date'] = data_value
                if j == 3:
                    if isinstance(data_value, str):
                        l1['end_date'] = datetime.datetime.strptime(
                            str(data_value), '%Y-%m-%d')
                    else:
                        l1['end_date'] = data_value
                if j == 4:
                    l1['status'] = data_value
                if j == 5:
                    # 服务人员id
                    organization_id = ''
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == rows[i][0].value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        organization_id = res[0]['id']
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == data_value) & (C('organization_id') == organization_id))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        l1['servicer_id'] = res[0]['id']
                    else:
                        l1['servicer_id'] = 'null'
                if j == 6:
                    l1['valuation_amount'] = int(data_value)
            data = get_info(l1, self.session)
            # 逐条插入（因为怕找不到上级Id）
            try:
                # count = a.cur.executemany(add_sql, l2)
                print('最终结果数据》》》', i)
                bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                            TypeId.serviceRecord.value, data, 'PT_Service_Record')
                # if bill_id:
                #     res = 'Success'
                #     return res
            except Exception as e:
                print(str(e))
        return 'success'

    def import_excel_user_nh(self, url):
        ''' 导入人员（长者）'''
        # url = "‪C:/Users/AdministratorDesktop/长者资料.xlsx"
        # 打开一个workbook
        wb = load_workbook(filename=url)

        # 获取当前活跃的worksheet,默认就是第一个worksheet
        # ws = wb.active

        # 当然也可以使用下面的方法

        # 获取所有表格(worksheet)的名字
        sheets = wb.get_sheet_names()
        # 第一个表格的名称

        sheet_first = sheets[0]
        # 获取特定的worksheet
        ws = wb.get_sheet_by_name(sheet_first)

        column_number = ws.max_column  # 最大的列
        row_number = ws.max_row  # 最大的行

        # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
        rows = list(ws.rows)
        columns = list(ws.columns)
        Data = ws.cell(row=1, column=column_number).value
        personnel_info = {
            "name": "",
            "personnel_category": "长者",  # 长者
            "sex": "",
            "telephone": "",
            "date_birth": "",
            "nation": "",
            "native_place": "",
            "address": "",
            "card_number": "",
            "remarks": "",
            "role_id": "8ed260f6-e355-11e9-875e-a0a4c57e9ebe",
            "family_name": "",
            "card_name": "",
            "id_card_address": "",
            'older_type': "",
        }
        for i in range(1, row_number):    # 从第二行开始遍历
            # 要保存的对象
            l1 = {}    # 完整的一条添加数据
            l1['personnel_info'] = personnel_info
            l1['organization_id'] = ''
            remarks = ''
            role = ''
            for j in range(0, column_number):    # 从第一列开始读取
                data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
                # print('data_value>>',data_value)
                if j == 0:
                    admin_id = ''
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('code') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_Administration_Division')
                    if len(res) > 0:
                        admin_id = res[0]['id']
                    l1['admin_area_id'] = admin_id
                if j == 1:
                    l1['personnel_info']['name'] = data_value
                    l1['name'] = data_value
                if j == 2:
                    l1['personnel_info']['sex'] = data_value
                if j == 3:
                    if isinstance(data_value, str):
                        l1['personnel_info']['date_birth'] = datetime.datetime.strptime(
                            str(data_value), '%Y-%m-%d')
                    else:
                        l1['personnel_info']['date_birth'] = data_value
                if j == 4:
                    l1['id_card'] = data_value
                    l1['id_card_type'] = '身份证'
                if j == 11:
                    l1['personnel_info']['address'] = data_value
                if j == 17:
                    l1['personnel_info']['telephone'] = data_value
                if j == 18:
                    l1['personnel_info']['nation'] = data_value
                if j == 5 or j == 6 or j == 7 or j == 8 or j == 9 or j == 10 or j == 19 or j == 20 or j == 21 or j == 22 or j == 23 or j == 24 or j == 25 or j == 26:
                    if data_value:
                        remarks = remarks + ',' + data_value
                if j == 28:
                    l1['personnel_info']['older_type'] = data_value
                if j == 27:
                    # 所属机构
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        l1['organization_id'] = res[0]['id']
                if j == 30:
                    role = data_value
            l1['personnel_type'] = '1'
            l1['personnel_info']['remarks'] = remarks
            # l1['login_info'] = [{
            #     "login_type" : "account",
            #     "login_check" : {
            #         "account_name" : "",
            #         "password" : "27bfaa8f76fe30db4d3d2fec408d0b29a772bdd3bf5ee330951233473d94d0e9"
            #     }
            # }]
            data = get_info(l1, self.session)
            # 新增账户信息（1个机构储蓄、1个app储蓄、1个真实账户、1个补贴账户）
            # 机构储蓄对象
            org_account_data = FinancialAccountObject(
                AccountType.account_recharg_wel, data['id'], l1['organization_id'], AccountStatus.normal, 1, AccountType.account_recharg_wel, {}, 0)
            org_account = get_info(org_account_data.to_dict(), self.session)
            # app储蓄对象
            app_account_data = FinancialAccountObject(
                AccountType.account_recharg_app, data['id'], plat_id, AccountStatus.normal, 2, AccountType.account_recharg_app, {}, 0)
            app_account = get_info(app_account_data.to_dict(), self.session)
            # 真实账户对象
            real_account_data = FinancialAccountObject(
                AccountType.account_real, data['id'], None, AccountStatus.normal, 3, AccountType.account_real, {}, 0)
            real_account = get_info(real_account_data.to_dict(), self.session)
            # 补贴账户对象
            subsidy_account_data = FinancialAccountObject(
                AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 4, AccountType.account_subsidy, {}, 0)
            subsidy_account = get_info(
                subsidy_account_data.to_dict(), self.session)
            tables = ['PT_User', FinancialAccount().name]
            datas = [[data], [org_account, app_account,
                              real_account, subsidy_account]]
            if l1['personnel_info']['personnel_category'] == '工作人员':
                # 插入set_role表
                role_id = '8f9809cc-e355-11e9-9d97-a0a4c57e9ebe'
                if role == '服务商':
                    role_id = '8f9809cc-e355-11e9-9d97-a0a4c57e9ebe'
                if role == '幸福院':
                    role_id = '8f3524cc-e355-11e9-9f87-a0a4c57e9ebe'
                if role == '民政局':
                    role_id = '8f1d4064-e355-11e9-b3a2-a0a4c57e9ebe'
                if role == '福利院':
                    role_id = '8e271ff0-e355-11e9-91a6-a0a4c57e9ebe'
                set_role = {'role_id': role_id,
                            'principal_account_id': data['id'], 'role_of_account_id': l1['organization_id']}
                set_role_data = get_info(set_role, self.session)
                datas.append(set_role_data)
                tables.append('PT_Set_Role')
            # 逐条插入（因为怕找不到上级Id）
            try:
                # count = a.cur.executemany(add_sql, l2)
                print('最终结果数据》》》', i)
                bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                            TypeId.user.value, datas, tables)
                # if bill_id:
                #     res = 'Success'
                #     return res
            except Exception as e:
                print(e)
        return res

    def import_excel_servicer(self):
        ''' 导入人员（服务人员）'''
        url = "C:\服务人员.xlsx"
        # 打开一个workbook
        wb = load_workbook(filename=url)

        # 获取当前活跃的worksheet,默认就是第一个worksheet
        # ws = wb.active

        # 当然也可以使用下面的方法

        # 获取所有表格(worksheet)的名字
        sheets = wb.get_sheet_names()
        # 第一个表格的名称

        sheet_first = sheets[0]
        # 获取特定的worksheet
        ws = wb.get_sheet_by_name(sheet_first)

        column_number = ws.max_column  # 最大的列
        row_number = ws.max_row  # 最大的行

        # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
        rows = list(ws.rows)
        columns = list(ws.columns)
        Data = ws.cell(row=1, column=column_number).value
        personnel_info = {
            "name": "",
            "personnel_category": "工作人员",
            "sex": "",
            "telephone": "",
            "date_birth": "",
            "nation": "",
            "native_place": "",
            "address": "",
            "card_number": "",
            "remarks": "",
            "role_id": "8ed260f6-e355-11e9-875e-a0a4c57e9ebe",
            "family_name": "",
            "card_name": "",
            "id_card_address": ""
        }
        for i in range(1, row_number):    # 从第二行开始遍历
            # 要保存的对象
            l1 = {}    # 完整的一条添加数据
            l1['personnel_info'] = personnel_info
            l1['organization_id'] = '0b96b62e-e363-11e9-93c4-a0a4c57e9eb1'  # 双鸭山
            remarks = ''
            role = '服务商'
            for j in range(0, column_number):    # 从第一列开始读取
                data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
                # print('data_value>>',data_value)
                if j == 0:
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        l1['organization_id'] = res[0]['id']
                        l1['admin_area_id'] = res[0]['admin_area_id']
                if j == 1:
                    l1['personnel_info']['name'] = data_value
                    l1['name'] = data_value
                if j == 2:
                    l1['id_card'] = data_value
                    l1['id_card_type'] = '身份证'
                if j == 3:
                    l1['personnel_info']['sex'] = data_value
                if j == 4:
                    if isinstance(data_value, str):
                        l1['personnel_info']['date_birth'] = datetime.datetime.strptime(
                            str(data_value), '%Y-%m-%d')
                    else:
                        l1['personnel_info']['date_birth'] = data_value
                if j == 5:
                    l1['personnel_info']['telephone'] = data_value
                if j == 6:
                    l1['personnel_info']['address'] = data_value
            l1['personnel_type'] = '1'
            l1['personnel_info']['remarks'] = remarks
            l1['login_info'] = [{
                "login_type": "account",
                "login_check": {
                    "account_name": "",
                    "password": "27bfaa8f76fe30db4d3d2fec408d0b29a772bdd3bf5ee330951233473d94d0e9"
                }
            }]
            data = get_info(l1, self.session)
            # 新增账户信息（1个机构储蓄、1个app储蓄、1个真实账户、1个补贴账户）
            # 机构储蓄对象
            org_account_data = FinancialAccountObject(
                AccountType.account_recharg_wel, data['id'], l1['organization_id'], AccountStatus.normal, 1, AccountType.account_recharg_wel, {}, 0)
            org_account = get_info(org_account_data.to_dict(), self.session)
            # app储蓄对象
            app_account_data = FinancialAccountObject(
                AccountType.account_recharg_app, data['id'], plat_id, AccountStatus.normal, 2, AccountType.account_recharg_app, {}, 0)
            app_account = get_info(app_account_data.to_dict(), self.session)
            # 真实账户对象
            real_account_data = FinancialAccountObject(
                AccountType.account_real, data['id'], None, AccountStatus.normal, 3, AccountType.account_real, {}, 0)
            real_account = get_info(real_account_data.to_dict(), self.session)
            # 补贴账户对象
            subsidy_account_data = FinancialAccountObject(
                AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 4, AccountType.account_subsidy, {}, 0)
            subsidy_account = get_info(
                subsidy_account_data.to_dict(), self.session)
            tables = ['PT_User', FinancialAccount().name]
            datas = [[data], [org_account, app_account,
                              real_account, subsidy_account]]
            if l1['personnel_info']['personnel_category'] == '工作人员':
                # 插入set_role表
                role_id = '8f9809cc-e355-11e9-9d97-a0a4c57e9ebe'
                if role == '服务商':
                    role_id = '8f9809cc-e355-11e9-9d97-a0a4c57e9ebe'
                if role == '幸福院':
                    role_id = '8f3524cc-e355-11e9-9f87-a0a4c57e9ebe'
                if role == '民政局':
                    role_id = '8f1d4064-e355-11e9-b3a2-a0a4c57e9ebe'
                if role == '福利院':
                    role_id = '8e271ff0-e355-11e9-91a6-a0a4c57e9ebe'
                set_role = {'role_id': role_id,
                            'principal_account_id': data['id'], 'role_of_account_id': l1['organization_id']}
                set_role_data = get_info(set_role, self.session)
                datas.append(set_role_data)
                tables.append('PT_Set_Role')
            # 逐条插入（因为怕找不到上级Id）
            try:
                # count = a.cur.executemany(add_sql, l2)
                print('最终结果数据》》》', i)
                bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                            TypeId.user.value, datas, tables)
                # if bill_id:
                #     res = 'Success'
                #     return res
            except Exception as e:
                print(e)
        return res

    def change_old_age_mes(self):
        _filter = MongoBillFilter()
        _filter.match(C('bill_status') == 'valid')\
            .project({'_id': 0})
        data_res = self.query(_filter, 'PT_Old_Age_Allowance')
        if len(data_res) > 0:
            for data in data_res:
                _filter = MongoBillFilter()
                _filter.match(C('id_card_num') == data['id_card_num'])\
                    .project({'_id': 0})
                data_list = self.query(_filter, 'PT_Old_Age_List')
                if len(data_list) > 0:
                    data['town_street'] = data_list[0]['town_street']
                    data['village'] = data_list[0]['village']
                    data['sex'] = data_list[0]['sex']
                    data['bank_card_num'] = data_list[0]['bank_card_num']
                    data['amount'] = data_list[0]['amount']

                    def process_func(db):
                        update_data(db, 'PT_Old_Age_Allowance', data, {
                                    'id': data['id'], 'bill_status': 'valid'})
                    process_db(self.db_addr, self.db_port, self.db_name,
                               process_func, self.db_user, self.db_pwd)
                    # print(data)
        return 'success'

    def import_excel_xf_product(self):
        ''' 导入幸福小栈产品 '''
        url = "C:/Users/yzy/Desktop/test/幸福小栈导入模版.xlsx"
        # 打开一个workbook
        wb = load_workbook(filename=url)

        # 获取当前活跃的worksheet,默认就是第一个worksheet
        # ws = wb.active

        # 获取所有表格(worksheet)的名字
        sheets = wb.get_sheet_names()
        # 第一个表格的名称

        sheet_first = sheets[0]
        # 获取特定的worksheet
        ws = wb.get_sheet_by_name(sheet_first)

        column_number = ws.max_column  # 最大的列
        row_number = ws.max_row  # 最大的行

        # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
        rows = list(ws.rows)
        columns = list(ws.columns)
        Data = ws.cell(row=1, column=column_number).value
        is_add = True
        for i in range(1, row_number):    # 从第二行开始遍历
            # 要保存的对象
            l1 = {
                "name": "",
                "introduce": "",
                "remarks": "",
                "state": 1,
                "is_assignable": "1",
                "is_external": "1",
                "is_recommend": "是",
                "organization_id": "7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67",
                "service_product_type": "居家服务",
                "remaining_times": "1",
                "picture_collection": [],
                "additonal_contract_terms": "",
                "service_product_introduce": "服务产品介绍",
                "is_service_item": "true",
                "service_item_id": "f4687c4c-27b2-11ea-82c9-005056882303",
                "valuation_formula": [
                    {
                        "name": "标准计价公式",
                        "formula": "float('幸福小栈产品单价')"
                    }
                ],
                "proportion": [
                    {
                        "title": "平台",
                        "contents": 0
                    },
                    {
                        "title": "服务商",
                        "contents": 1
                    },
                    {
                        "title": "慈善",
                        "contents": 0
                    }
                ],
                "service_option": [
                    {
                        "default_value": "0",
                        "id": "d4baafb4-27b2-11ea-abb7-005056882303",
                        "is_modification_mermissible": "是",
                        "name": "幸福小栈产品单价",
                        "option_type": "service_before",
                        "option_value_type": "3",
                        "remarks": "幸福小栈产品单价",
                        "option_value": "0"
                    }
                ],
            }
            orgProduct = {
                'org_id': '',
                'product_list': []
            }
            data = get_info(l1, self.session)
            for j in range(0, column_number):    # 从第一列开始读取
                data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
                if j == 0:
                    data['name'] = data_value
                if j == 1:
                    data['introduce'] = data_value
                if j == 2:
                    data['service_option'][0]['option_value'] = str(data_value)
                if j == 3:
                    data['picture_collection'] = ['/build/upload/'+data_value]
                if j == 4:
                    # 所属机构
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == data_value))\
                        .project({'_id': 0})
                    res = self.query(_filter, 'PT_User')
                    if len(res) > 0:
                        organization_id = res[0]['id']
                        data['organization_id'] = organization_id
                        _filter = MongoBillFilter()
                        _filter.match_bill((C('org_id') == organization_id))\
                            .project({'_id': 0})
                        res_org_product = self.query(_filter, 'BS_Org_Product')
                        orgProduct['org_id'] = organization_id
                        if len(res_org_product) > 0:
                            # 已存在
                            orgProduct['product_list'] = res_org_product[0]['product_list'] + [data['id']]
                            orgProduct['id'] = res_org_product[0]['id']
                            is_add = False
                        else:
                            # 未存在
                            orgProduct['product_list'] = [data['id']]
                            is_add = True
                    else:
                        return '找不到:'+data_value+'对应的幸福院'

            # 逐条插入（因为怕找不到上级Id）
            try:
                # count = a.cur.executemany(add_sql, l2)
                print('最终结果数据》》》', i)
                if is_add:
                    bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                                TypeId.businessArea.value, [data, orgProduct], ['PT_Service_Product', 'BS_Org_Product'])
                else:
                    bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                                TypeId.businessArea.value, [data], ['PT_Service_Product'])
                    bill_id = self.bill_manage_service.add_bill(OperationType.update.value,
                                                                TypeId.businessArea.value, [orgProduct], ['BS_Org_Product'])
            except Exception as e:
                print(str(e))
        return 'success'

    def import_picture_files(self, file_path):
        ''' 读取文件夹下图片，并保存在服务器路径中'''
        directory_name = file_path
        for filename in os.listdir(directory_name):
            img = cv.imread(directory_name + "/" + filename)
            #####显示图片#######
            # cv.imshow(filename, img)
            # cv.waitKey(0)
            #####################

            #####保存图片#########
            time_id = 'xfxz'
            upload_path = "{0}\\{1}".format(self.upload_file,
                                            time_id)  # 以xfxz命名文件
            file_path = os.path.join(self.web_path,
                                     upload_path).replace('\\', '/')
            if not os.path.exists(file_path):  # 不存在改目录则会自动创建
                os.makedirs(file_path)
            save_path = os.path.join(file_path, filename).replace(
                '\\', '/')  # windows下路径要转化
            cv.imwrite(save_path, img)

        return 'Success'

    def get_url_upload_url(self, url_path):
        return get_url_upload_url(url_path)

    def insert_government_subsidy_user(self):
        _filter = MongoBillFilter()
        _filter.match_bill((C('personnel_info.personnel_classification').inner(['10f68676-1c8f-11ea-957f-144f8a6221df', '1246cab2-1c8f-11ea-b9e8-144f8a6221df', '13902edb-1c8f-11ea-bd43-144f8a6221df', '14de96ee-1c8f-11ea-9938-144f8a6221df', '162af016-1c8f-11ea-b323-144f8a6221df', '1779b60e-1c8f-11ea-8c81-144f8a6221df', '18c8b7c7-1c8f-11ea-ab48-144f8a6221df', '1a16f45e-1c8f-11ea-8703-144f8a6221df', '1b5b72b1-1c8f-11ea-b1db-144f8a6221df', '1ca6d1ad-1c8f-11ea-9ea5-144f8a6221df', '1df6c0b6-1c8f-11ea-8710-144f8a6221df', '1f421022-1c8f-11ea-9910-144f8a6221df', '2090e20b-1c8f-11ea-bacd-144f8a6221df', '21e0c295-1c8f-11ea-ab1e-144f8a6221df'])))\
            .project({'_id': 0})
        res_uer_product = self.query(_filter, 'PT_User')
        print(len(res_uer_product))
        # if len(res_uer_product)>0:
        #     for index, res_data in enumerate(res_uer_product):
        #         data={
        #             "user_id" : res_data['id'],
        #             "description" : "政府补贴长者",
        #             "organization_id" : "7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67",
        #             "classification_id" : res_data['personnel_info']['personnel_classification'],
        #             "expand" : {},
        #         }
        #         data_info  = get_info(data,self.session)
        #         bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
        #                                                         TypeId.businessArea.value, [data_info], ['PT_Government_Subsidy_User'])
        #         print('成功导入第'+str(index)+'行，长者id:'+res_data['id'])
        return 'Success'

    def import_permission(self):
        # funct = ['首页', '老友圈', '服务热线', '搜索', '我的']
        # funct = ['首页', '资讯发布', '消息提醒', '搜索', '我的']
        funct = ['发布', '签到', '受理']
        # funct = ['发帖', '评论', '点赞']
        # funct = ['服务人员订单']
        for f in funct:
            role_ids = ['8e271ff0-e355-11e9-91a6-a0a4c57e9ebe', '8e5203ca-e355-11e9-b94c-a0a4c57e9ebe', '8e65a5a6-e355-11e9-8161-a0a4c57e9ebe', '8e802000-e355-11e9-8707-a0a4c57e9ebe', '8e9ae8dc-e355-11e9-a6f3-a0a4c57e9ebe', '8eaf3fba-e355-11e9-b97e-a0a4c57e9ebe', '8ebfa90a-e355-11e9-b46e-a0a4c57e9ebe', '8ee602b4-e355-11e9-959a-a0a4c57e9ebe', '8ef9cb14-e355-11e9-a07e-a0a4c57e9ebe', '8f0aaf90-e355-11e9-a83c-a0a4c57e9ebe',
                        '8f1d4064-e355-11e9-b3a2-a0a4c57e9ebe', '8f3524cc-e355-11e9-9f87-a0a4c57e9ebe', '8f4cbe36-e355-11e9-84e4-a0a4c57e9ebe', '8f5feb12-e355-11e9-a716-a0a4c57e9ebe', '8f72e614-e355-11e9-a8b8-a0a4c57e9ebe', '8f880d68-e355-11e9-a39d-a0a4c57e9ebe', '8f9809cc-e355-11e9-9d97-a0a4c57e9ebe', '8e271jjue87ff0-e355-11e9-91a6-a0a4c57e9ebe', '876aaef6-1661-11ea-9497-144f8a6221df', '295593c2-2088-11ea-99a9-005056882303']
            for role_id in role_ids:
                data = {
                    "role_id": role_id,
                    "function": f,
                    "permission": "查询",
                    "remarks": "",
                    "is_allow_super_use": True,
                }
                data_info = get_info(data, self.session)
                bill_id = self.bill_manage_service.add_bill(OperationType.add.value,
                                                            TypeId.businessArea.value, [data_info], ['PT_Permission'])
        return 'Success'
